import { Router } from 'express';
import { body } from 'express-validator';
import validateFormData from '../helpers/validate_form.js';
import DB from '../models/db.js';
import XLSX from 'xlsx';


const router = Router();




/**
 * Route to list enrollments records
 * @GET /enrollments/index/{fieldname}/{fieldvalue}
 */
router.get(['/', '/index/:fieldname?/:fieldvalue?'], async (req, res) => {  
	try{
		const query = {};
		let queryFilters = [];
		let where = {};
		let replacements = {};
		let fieldName = req.params.fieldname;
		let fieldValue = req.params.fieldvalue;
		
		if (fieldName){
			queryFilters.push(DB.filterBy(fieldName, fieldValue));
		}
		let search = req.query.search;
		if(search){
			let searchFields = DB.Enrollments.searchFields();
			where[DB.op.or] = searchFields;
			replacements.search = `%${search}%`;
		}

		let allowedRoles = ["admin","eduadmin"];
		let userRole = req.userRoleName;
		if(!allowedRoles.includes(userRole)){
			if(userRole === 'student')where['student_id'] = req.user.user_id;
			if(userRole === 'teacher')where['teacher_id'] = req.user.user_id;
		}

		if(queryFilters.length){
			where[DB.op.and] = queryFilters;
		}

		query.raw = true;
		query.where = where;
		query.replacements = replacements;
		query.order = DB.getOrderBy(req, 'enrollment_id', 'desc');
		query.attributes = DB.Enrollments.listFields();
		
		// 添加与 users 表的关联查询
		query.include = [{
			model: DB.Users,
			as: 'teacher',
			attributes: ['name'],
			required: false
		}];

		let page = parseInt(req.query.page) || 1;
		let limit = parseInt(req.query.limit) || 10;
		let result = await DB.Enrollments.paginate(query, page, limit);

		// 处理结果，将教师姓名添加到每条记录中
		if (result.records) {
			result.records = result.records.map(record => ({
				...record,
				teacher_name: record['teacher.name'] || '未知教师'
			}));
		}

		return res.ok(result);
	}
	catch(err) {
		return res.serverError(err);
	}
});


/**
 * Route to view Enrollments record
 * @GET /enrollments/view/{recid}
 */
router.get('/view/:recid', async (req, res) => {
	try{
		const recid = req.params.recid || null;
		const query = {}
		const where = {}
		where['enrollment_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Enrollments.viewFields();
		let record = await DB.Enrollments.findOne(query);
		if(!record){
			return res.notFound();
		}
		return res.ok(record);
	}
	catch(err){
		return res.serverError(err);
	}
});


/**
 * Route to insert Enrollments record
 * @POST /enrollments/add
 */
router.post('/add/', 
	[
		body('enrollment_id').not().isEmpty().isNumeric(),
		body('student_id').optional({nullable: true, checkFalsy: true}).isNumeric(),
		body('course_id').optional({nullable: true, checkFalsy: true}).isNumeric(),
		body('enrollment_date').not().isEmpty(),
		body('status').optional({nullable: true, checkFalsy: true}),
	], validateFormData
, async function (req, res) {
	try{
		let modeldata = req.getValidFormData();
		
		//save Enrollments record
		let record = await DB.Enrollments.create(modeldata);
		//await record.reload(); //reload the record from database
		const recid =  record['enrollment_id'];
		
		return res.ok(record);
	} catch(err){
		return res.serverError(err);
	}
});


/**
 * Route to get  Enrollments record for edit
 * @GET /enrollments/edit/{recid}
 */
router.get('/edit/:recid', async (req, res) => {
	try{
		const recid = req.params.recid;
		const query = {};
		const where = {};
		where['enrollment_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Enrollments.editFields();
		let record = await DB.Enrollments.findOne(query);
		if(!record){
			return res.notFound();
		}
		return res.ok(record);
	}
	catch(err){
		return res.serverError(err);
	}
});


/**
 * Route to update  Enrollments record
 * @POST /enrollments/edit/{recid}
 */
router.post('/edit/:recid', 
	[
		body('status').optional({nullable: true}).isIn(['enrolled', 'unenrolled'])
			.withMessage('状态只能是 enrolled 或 unenrolled'),
		body('enrollment_date').optional({nullable: true}).not().isEmpty()
	], validateFormData
, async (req, res) => {
	try{
		const recid = req.params.recid;
		let modeldata = req.getValidFormData({ includeOptionals: true });
		
		const query = {};
		const where = {};
		where['enrollment_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Enrollments.editFields();
		
		let record = await DB.Enrollments.findOne(query);
		if(!record){
			return res.notFound();
		}
		
		// 只更新状态
		await DB.Enrollments.update({
			status: modeldata.status,
			enrollment_date: modeldata.enrollment_date
		}, {where: where});
		
		// 获取更新后的记录
		record = await DB.Enrollments.findOne({
			where: where,
			include: [{
				model: DB.Users,
				as: 'teacher',
				attributes: ['name', 'username'],
				required: false
			}],
			raw: true
		});

		// 返回更新后的记录
		return res.ok({
			...record,
			teacher_name: record['teacher.name'] || '未知教师',
			teacher_id: record['teacher.username']
		});
	}
	catch(err){
		console.error('更新选课状态时出错:', err);
		return res.serverError(err);
	}
});


/**
 * Route to delete Enrollments record by table primary key
 * Multi delete supported by recid separated by comma(,)
 * @GET /enrollments/delete/{recid}
 */
router.get('/delete/:recid', async (req, res) => {
	try{
		const recid = (req.params.recid || '').split(',');
		const query = {};
		const where = {};
		where['enrollment_id'] = recid;
		query.raw = true;
		query.where = where;
		let records = await DB.Enrollments.findAll(query);
		records.forEach(async (record) => { 
			//perform action on each record before delete
		});
		await DB.Enrollments.destroy(query);
		return res.ok(recid);
	}
	catch(err){
		return res.serverError(err);
	}
});

/**
 * Route to download enrollments template
 * @GET /enrollments/template
 */
router.get('/template', async (req, res) => {
    try {
        // 获取所有有效课程信息
        const courses = await DB.Courses.findAll({
            where: {
                status: 'active'  // 只获取有效的课程
            },
            attributes: ['course_id', 'course_name', 'teacher_id'],
            raw: true
        });

        const workbook = XLSX.utils.book_new();
        
        // 定义表头
        const headers = [
            ['学号', '课程ID', '课程名称', '教师工号'],
            ['student_id', 'course_id', 'course_name', 'teacher_id']
        ];

        // 为每个课程生成一行，只填充课程ID和课程名称
        const dataRows = courses.map(course => [
            '',  // 学号留空，由用户填写
            course.course_id,  // 自动填充课程ID
            course.course_name,  // 自动填充课程名称
            ''  // 教师工号留空，由用户填写
        ]);

        // 合并headers和数据行
        const data = [...headers, ...dataRows];

        // Create worksheet
        const worksheet = XLSX.utils.aoa_to_sheet(data);
        
        // 设置列宽
        const colWidths = [
            { wch: 15 }, // student_id
            { wch: 10 }, // course_id
            { wch: 30 }, // course_name
            { wch: 15 }  // teacher_id
        ];
        worksheet['!cols'] = colWidths;

        // 设置课程ID和课程名称列为灰色背景
        const range = XLSX.utils.decode_range(worksheet['!ref']);
        for (let R = 2; R <= range.e.r; R++) {  // 从第3行开始（跳过表头）
            for (let C = 1; C <= 2; C++) {  // 设置第2列（课程ID）和第3列（课程名称）
                const cellRef = XLSX.utils.encode_cell({ r: R, c: C });
                if (!worksheet[cellRef]) continue;
                
                worksheet[cellRef].s = {  // 设置单元格样式
                    fill: {
                        fgColor: { rgb: "EEEEEE" },  // 浅灰色背景
                        patternType: "solid"
                    }
                };
            }
        }

        // 打印一下数据，方便调试
        console.log('Template data:', courses);

        XLSX.utils.book_append_sheet(workbook, worksheet, 'Enrollments');

        // Generate buffer
        const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

        // Set response headers
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', 'attachment; filename=enrollments_template.xlsx');
        res.setHeader('Content-Length', buffer.length);

        // Send file
        res.send(buffer);
    } catch (err) {
        console.error('Template generation error:', err);
        console.error('Error details:', err.message);
        return res.serverError(err);
    }
});

/**
 * Route to bulk import enrollments
 * @POST /enrollments/bulk-import
 */
router.post('/bulk-import', async (req, res) => {
    try {
        const enrollments = req.body;
        const results = [];

        console.log('开始处理导入数据:', enrollments);

        for (const enrollment of enrollments) {
            try {
                // 检查必要字段是否存在
                if (!enrollment.student_id || !enrollment.course_name || !enrollment.teacher_id) {
                    results.push({
                        success: false,
                        error: '缺少必要字段（学号、课程名称或教师工号）',
                        data: enrollment
                    });
                    continue;
                }

                // 查找学生 - 使用username
                const student = await DB.Users.findOne({
                    where: { username: enrollment.student_id },
                    attributes: ['user_id', 'name', 'username'],
                    raw: true
                });

                console.log('查找到的学生信息:', student);

                if (!student) {
                    results.push({
                        success: false,
                        error: `学生不存在 (学号: ${enrollment.student_id})，请确保填写的是正确的学号`,
                        data: enrollment
                    });
                    continue;
                }

                // 先查找教师信息
                const teacher = await DB.Users.findOne({
                    where: { username: enrollment.teacher_id },  // 同样使用username查找教师
                    attributes: ['user_id', 'name', 'username'],
                    raw: true
                });

                console.log('查找到的教师信息:', teacher);

                if (!teacher) {
                    results.push({
                        success: false,
                        error: `教师不存在 (教师工号: ${enrollment.teacher_id})`,
                        data: enrollment
                    });
                    continue;
                }

                // 查找课程
                const course = await DB.Courses.findOne({
                    where: { 
                        course_name: enrollment.course_name,
                        teacher_id: enrollment.teacher_id,  // 直接使用teacher_id，因为courses表中存的就是教师工号
                        status: 'active'
                    },
                    attributes: ['course_id', 'course_name', 'teacher_id'],
                    raw: true
                });

                // 输出更详细的调试信息
                console.log('课程查询条件:', {
                    course_name: enrollment.course_name,
                    teacher_id: enrollment.teacher_id
                });
                console.log('查找到的课程信息:', course);

                // 如果没找到，查询该教师的所有课程
                if (!course) {
                    const teacherCourses = await DB.Courses.findAll({
                        where: { 
                            teacher_id: enrollment.teacher_id,
                            status: 'active'
                        },
                        attributes: ['course_name', 'teacher_id'],
                        raw: true
                    });
                    
                    console.log('该教师的所有课程:', teacherCourses);
                    
                    results.push({
                        success: false,
                        error: `未找到匹配的课程 (课程名称: ${enrollment.course_name}, 教师工号: ${enrollment.teacher_id})
该教师的可选课程: ${teacherCourses.map(c => `\n- ${c.course_name}`)}`,
                        data: enrollment
                    });
                    continue;
                }

                // 检查是否已经选过这门课
                const existingEnrollment = await DB.Enrollments.findOne({
                    where: {
                        student_id: student.user_id,  // 使用查找到的学生的user_id
                        course_id: course.course_id
                    },
                    raw: true
                });

                if (existingEnrollment) {
                    results.push({
                        success: false,
                        error: `该学生已选择此课程 (学号: ${student.username}, 课程: ${enrollment.course_name})`,
                        data: enrollment
                    });
                    continue;
                }

                // 创建新的选课记录
                // 获取中国时区的当前时间
                const now = new Date(new Date().toLocaleString('en-US', { timeZone: 'Asia/Shanghai' }));

                // 获取最大的enrollment_id
                const maxIdResult = await DB.Enrollments.findOne({
                    attributes: [[DB.sequelize.fn('MAX', DB.sequelize.col('enrollment_id')), 'maxId']],
                    raw: true
                });
                const nextId = (maxIdResult.maxId || 0) + 1;

                const newEnrollment = await DB.Enrollments.create({
                    enrollment_id: nextId,
                    student_id: student.user_id,
                    course_id: course.course_id,
                    teacher_id: teacher.user_id,
                    enrollment_date: now,
                    status: 'enrolled',
                    created_at: now,
                    updated_at: now
                });

                results.push({
                    success: true,
                    data: {
                        enrollment_id: nextId,
                        student_id: student.username,
                        student_name: student.name,
                        course_name: enrollment.course_name,
                        teacher_id: teacher.username,  // 返回教师工号而不是user_id
                        enrollment_date: newEnrollment.enrollment_date,
                        status: newEnrollment.status
                    }
                });
            } catch (error) {
                console.error('处理选课记录时出错:', error);
                results.push({
                    success: false,
                    error: `处理失败: ${error.message}`,
                    data: enrollment
                });
            }
        }

        res.ok(results);
    } catch (err) {
        console.error('批量导入整体失败:', err);
        return res.serverError(err);
    }
});

export default router;
